﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Sispe.Entities;
using System.Data;
using System.Data.Objects;



namespace Sispe.Repository
{
    public class DerechoHabienteRepository:MasterRepository, IDerechoHabienteRepository
    {
        public List<DERECHO_HABIENTE> GetAllFromDerechoHabientes()
        {
            var query = from s in context.DERECHO_HABIENTE
                        .Include("PERSONA")
                        .Include("DOCUMENTO_SUSTENTA")
                        .Include("VINCULO_FAMILIAR")
                        select s;

            ObjectContext.Refresh(RefreshMode.StoreWins, query);
            
            return query.ToList();
        }

        public List<DERECHO_HABIENTE> GetFromDerechoHabienteByIdTrabajador(int idtrabajador)
        {
            var query = from dh in context.DERECHO_HABIENTE
                        .Include("PERSONA")
                        .Include("DOCUMENTO_SUSTENTA")
                        .Include("VINCULO_FAMILIAR")
                        where dh.IdTrabajador.Equals(idtrabajador)
                        select dh;
            ObjectContext.Refresh(RefreshMode.StoreWins, query);
            return query.ToList();
        }

        public List<DERECHO_HABIENTE> GetFromDerechoHabienteByDni(string dni)
        {
            var query = from s in context.DERECHO_HABIENTE
                        .Include("PERSONA")
                        .Include("DOCUMENTO_SUSTENTA")
                        .Include("VINCULO_FAMILIAR")
                        select s;
            if (dni != null && dni != string.Empty && dni != "")
            {
                query = from s in context.DERECHO_HABIENTE
                        where s.PERSONA.Dni.Contains(dni) 
                        select s;
            }
            ObjectContext.Refresh(RefreshMode.StoreWins, query);
            return query.ToList();
        }

        public List<DERECHO_HABIENTE> GetFromDerechoHabienteByDescripcion(string descripcion)
        {
            var query = from s in context.DERECHO_HABIENTE
                        .Include("PERSONA")
                        .Include("DOCUMENTO_SUSTENTA")
                        .Include("VINCULO_FAMILIAR")
                        select s;
            if (descripcion != null && descripcion != string.Empty && descripcion != "")
            {
                query = from s in context.DERECHO_HABIENTE
                        where s.PERSONA.Nombres.Contains(descripcion)||
                        s.PERSONA.ApeMaterno.Contains(descripcion)||
                        s.PERSONA.ApePaterno.Contains(descripcion)
                        select s;
            }
            ObjectContext.Refresh(RefreshMode.StoreWins, query);
            return query.ToList();
        }

        public void RegistrarDerechoHabiente(DERECHO_HABIENTE derechohabiente)
        {
            context.Entry(derechohabiente.PERSONA).State = EntityState.Unchanged;
            context.Entry(derechohabiente.DOCUMENTO_SUSTENTA).State = EntityState.Unchanged;
            context.Entry(derechohabiente.VINCULO_FAMILIAR).State = EntityState.Unchanged;
            context.Entry(derechohabiente.TRABAJADOR).State = EntityState.Unchanged;
            context.DERECHO_HABIENTE.Add(derechohabiente);
            context.SaveChanges();
        }

        public void ModificarDerechoHabiente(DERECHO_HABIENTE derechohabiente)
        {
            derechohabiente.PERSONA = null;
            derechohabiente.DOCUMENTO_SUSTENTA = null;
            derechohabiente.VINCULO_FAMILIAR = null;
            derechohabiente.TRABAJADOR = null;

            context.Entry(derechohabiente).State = System.Data.EntityState.Modified;

            context.SaveChanges();
        }


        public DERECHO_HABIENTE GetFromDerechoHabienteById(int id)
        {
            var query = from s in context.DERECHO_HABIENTE
                       .Include("PERSONA")
                       .Include("DOCUMENTO_SUSTENTA")
                       .Include("VINCULO_FAMILIAR")
                        where s.IdDerechoHabiente.Equals(id)
                        select s;
            ObjectContext.Refresh(RefreshMode.StoreWins, query);
            return query.SingleOrDefault();
        }


        public void RemoveDerechoHabiente(int id)
        {
            var existe = context.DERECHO_HABIENTE.Find(id);

            if (existe != null)
            {
                context.DERECHO_HABIENTE.Remove(existe);
                context.SaveChanges();
            }
        }


        public List<DERECHO_HABIENTE> GetFromDerechoHabienteByVinculoFam(string vinculo)
        {
            var query = from s in context.DERECHO_HABIENTE
                        .Include("PERSONA")
                        .Include("DOCUMENTO_SUSTENTA")
                        .Include("VINCULO_FAMILIAR")
                        select s;
            if (vinculo != null && vinculo != string.Empty && vinculo != "")
            {
                query = from s in context.DERECHO_HABIENTE
                        where s.VINCULO_FAMILIAR.Descripcion.Contains(vinculo)
                        select s;
            }
            ObjectContext.Refresh(RefreshMode.StoreWins, query);
            return query.ToList();
        }

        public List<DERECHO_HABIENTE> GetFromDerechoHabienteByDocumentoSust(string documento)
        {
            var query = from s in context.DERECHO_HABIENTE
                        .Include("PERSONA")
                        .Include("DOCUMENTO_SUSTENTA")
                        .Include("VINCULO_FAMILIAR")
                        select s;
            if (documento != null && documento != string.Empty && documento != "")
            {
                query = from s in context.DERECHO_HABIENTE
                        where s.DOCUMENTO_SUSTENTA.Descripcion.Contains(documento)
                        select s;
            }
            ObjectContext.Refresh(RefreshMode.StoreWins, query);
            return query.ToList();
        }


        public List<DERECHO_HABIENTE> GetFromDerechoHabienteByActico(string nombre, string vinculo, string documento, bool activo, bool constancia)
        {
            if (activo == true)
            {
                if (nombre != "")
                {
                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")
                                where s.Activo == activo
                                select s;

                    if (nombre != null && nombre != string.Empty && nombre != "")
                    {
                        query = from s in query
                                where s.PERSONA.Nombres.Contains(nombre) ||
                                s.PERSONA.ApeMaterno.Contains(nombre) ||
                                s.PERSONA.ApePaterno.Contains(nombre)
                                select s;
                    }

                    else //retornar todos
                    {
                        query = from h in query
                                select h;
                    }
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }
                if (vinculo != "")
                {
                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")
                                where s.Activo == activo
                                select s;

                    if (vinculo != null && vinculo != string.Empty && vinculo != "")
                    {
                        query = from s in query
                                where s.VINCULO_FAMILIAR.Descripcion.Contains(vinculo) 
                                select s;
                    }

                    else //retornar todos
                    {
                        query = from h in query
                                select h;
                    }
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }
                if (documento != "")
                {
                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")
                                where s.Activo == activo
                                select s;

                    if (documento != null && documento != string.Empty && documento != "")
                    {
                        query = from s in query
                                where s.DOCUMENTO_SUSTENTA.Descripcion.Contains(documento) 
                                select s;
                    }

                    else //retornar todos
                    {
                        query = from h in query
                                select h;
                    }
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }
                if (constancia == true )
                {
                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")
                                where s.Activo == activo && s.ConstanciaFisica == constancia
                                select s;
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }

                else
                {

                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")
                                select s;
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }
            }



                ////////////////////////////////////////
            else
            {
                //var query = from s in context.DERECHO_HABIENTE
                //           .Include("PERSONA")
                //           .Include("DOCUMENTO_SUSTENTA")
                //           .Include("VINCULO_FAMILIAR")
                //            select s;
                //if (nombre != null && nombre != string.Empty && nombre != "")
                //{
                //    query = from s in context.DERECHO_HABIENTE
                //            where s.PERSONA.Nombres.Contains(nombre) ||
                //            s.PERSONA.ApeMaterno.Contains(nombre) ||
                //            s.PERSONA.ApePaterno.Contains(nombre)
                //            select s;
                //}
                //return query.ToList();

                if (nombre != "")
                {
                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")
                                select s;

                    if (nombre != null && nombre != string.Empty && nombre != "")
                    {
                        query = from s in query
                                where s.PERSONA.Nombres.Contains(nombre) ||
                                s.PERSONA.ApeMaterno.Contains(nombre) ||
                                s.PERSONA.ApePaterno.Contains(nombre)
                                select s;
                    }

                    else //retornar todos
                    {
                        query = from h in query
                                select h;
                    }
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }
                if (vinculo != "")
                {
                    var query = from s in context.DERECHO_HABIENTE.Include("PERSONA")
                                                                    .Include("DOCUMENTO_SUSTENTA")
                                                                    .Include("VINCULO_FAMILIAR")
                                select s;

                    if (vinculo != null && vinculo != string.Empty && vinculo != "")
                    {
                        query = from s in query
                                where s.VINCULO_FAMILIAR.Descripcion.Contains(vinculo)
                                select s;
                    }

                    else //retornar todos
                    {
                        query = from h in query
                                select h;
                    }
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }
                if (documento != "")
                {
                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")
                                select s;

                    if (documento != null && documento != string.Empty && documento != "")
                    {
                        query = from s in query
                                where s.DOCUMENTO_SUSTENTA.Descripcion.Contains(documento)
                                select s;
                    }

                    else //retornar todos
                    {
                        query = from h in query
                                select h;
                    }
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }
                if (constancia == true)
                {
                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")
                                where s.ConstanciaFisica == constancia
                                select s;
                    ObjectContext.Refresh(RefreshMode.StoreWins, query);
                    return query.ToList();
                }
                else
                {

                    var query = from s in context.DERECHO_HABIENTE
                .Include("PERSONA")
                .Include("DOCUMENTO_SUSTENTA")
                .Include("VINCULO_FAMILIAR")

                                select s;

                    ObjectContext.Refresh(RefreshMode.StoreWins, query);

                    return query.ToList();
                }
            }
        }
    }
}
